#include "mydatabase.h"
#include <QSqlError>
#include <QMessageBox>
#include <QDir>

#define AUTO_INCREMENT "autoincrement"


const QString MyDataBase::TAB_VERSION = "tab_version";
const QString MyDataBase::TAB_STRUCTURE = "tab_structure";
const QString MyDataBase::_ID ="_id";

const QString MyDataBase::_TAB_VERNUM = "ver_num";
const QString MyDataBase::_TAB_VERVALUE = "ver_value";
const QString MyDataBase::_TAB_COMPONENT_NAME = "component_name";
const QString MyDataBase::_TAB_COMPONENT_TYPE = "component_type";
const QString MyDataBase::_TAB_COMPONENT_BELONG = "component_belong";



MyDataBase *MyDataBase::mDbInstance = NULL;
MyDataBase::MyDataBase(QObject *parent)
    : QObject(parent)
{
#ifdef WIN32
const QString DATABASE_NAME = "F:\\work\\dynamic_map_tool\\bin\\tool.db";
#else
const QString DATABASE_NAME = QDir::currentPath() + "tool.db";
#endif
   mDb = QSqlDatabase::addDatabase("QSQLITE");
   mDb.setHostName("huaqi");
   mDb.setDatabaseName(DATABASE_NAME);
   mDb.setUserName("admin");
   mDb.setPassword("123456");
   if(!mDb.open())
       qDebug("open db failed!");
   QStringList tabs = mDb.tables();

   if (!tabs.contains(TAB_VERSION)){
         createVersionTable(mDb);
      }

   if (!tabs.contains(TAB_STRUCTURE)) {
       createStructureTable(mDb);
   }
   else
   {
      initStructureTable(mDb);
   }


}

MyDataBase* MyDataBase::getDataBasePtr()
{
    if (mDbInstance == NULL)
    {
        mDbInstance  = new MyDataBase();
    }
    return mDbInstance;
}

QSqlDatabase &MyDataBase::getDataBase()
{
    return getDataBasePtr()->mDb;
}

void MyDataBase::createVersionTable(QSqlDatabase &db)
{
    DataBaseQuery query(db);
    query.exec("drop table " + TAB_VERSION);
    QString str = "create table " + TAB_VERSION + "(\n" +
            _ID + " integer primary key "  AUTO_INCREMENT", \n" +
            _TAB_VERNUM + " int(64) DEFAULT 0, \n" +
            _TAB_VERVALUE + " int(64) DEFAULT 0 \n" +
            ")";
    query.execString(str);

    QMap<QString, QVariant> map_init;
    map_init.insert(_TAB_VERNUM, QVariant(VERSION));
    map_init.insert(_TAB_VERVALUE, QVariant(1));

    query.insert(TAB_VERSION, map_init);

}

qint64  MyDataBase::getDataBaseVersion(QSqlDatabase &db)
{
    qint64 ver;
    DataBaseQuery query(db);
    QString str = "select tab_version.ver_value from tab_version where tab_version.ver_num = " + QString::number(VERSION);
    query.exec(str);
    if (query.next())
    {
        ver = query.value(0).toLongLong();
    }
    else
    {
        ver = 0;
    }
    return ver;
}

void MyDataBase::setDataBaseVersion(qint64 value, QSqlDatabase &db)
{
    DataBaseQuery query(db);
    QString str;
    str="select tab_version.ver_num from tab_version where tab_version.ver_num = " + QString::number(VERSION);
    query.execString(str);

    if(query.next())
    {
        QMap<QString,QVariant> map;
        QString option = _TAB_VERNUM + " = '" + QString::number(VERSION) +"'";
        map.insert(_TAB_VERVALUE, QVariant(value));
        query.updateItem(TAB_VERSION, map, option);
    }
    else
    {
        QMap<QString,QVariant> map;
        map.insert(_TAB_VERNUM, VERSION);
        map.insert(_TAB_VERVALUE, value);
        query.insert(TAB_VERSION, map);
    }
}

void MyDataBase::createStructureTable(QSqlDatabase &db)
{
    DataBaseQuery query(db);
    query.exec("drop table " + TAB_STRUCTURE);
    QString str = "create table " + TAB_STRUCTURE + "(\n" +
            _ID + " integer primary key " AUTO_INCREMENT", \n" +
            _TAB_COMPONENT_NAME + " varchar(255), \n" +
            _TAB_COMPONENT_TYPE + " varchar(255), \n" +
            _TAB_COMPONENT_BELONG + " varchar(255) \n" +
            ")";
    query.execString(str);
}

void MyDataBase::initStructureTable(QSqlDatabase &db)
{
    qint64 version = getDataBaseVersion(db);
    if(version < DataBaseVersion)
    {
        createStructureTable(db);
    }
}



DataBaseQuery::DataBaseQuery(QSqlDatabase db):QSqlQuery(db)
{

}

bool DataBaseQuery::containColumn(QString tabName, QString column)
{
    QString str = "select * from "+ tabName +" where " + column +"= txdfasf";
    return exec(str);
}

bool DataBaseQuery::addColumn(QString tabName, QString column, QString option)
{
    QString str = "ALTER TABLE "+ tabName +" ADD " + column +" "+option;
    return exec(str);
}

bool DataBaseQuery::selectTable(QString tabName, const QStringList *keyList, QString option, QString shortItem, bool shortdesc)
{
    QString str = "select DISTINCT ";
    if(keyList == NULL)
    {
        str+="* ";
    }
    else
    {
        int i = 0;
        for(i=0;i<keyList->count()-1;i++)
        {
            str+=keyList->at(i);
            str+=',';
        }
        str+=keyList->at(i);

    }
    str+=" from ";
    str+=tabName;

    if(option.length()!=0)
        str+=" where " +option;

    if(shortItem.length()!=0)
    {
        QStringList list=shortItem.split(',');
        int t = list.size();

        str+=" order by  ";
        for(int i=0;i<t-1;i++)
        {
            str+=list.at(i);
            str+=",";
        }
        str+=list.at(t-1);


        if(shortdesc)
        {
            str+= " desc";
        }
        else
        {
            str+= " asc";
        }
    }
    if(exec(str)==false)
    {
        QMessageBox::critical(NULL,"Error",
                              lastError().databaseText()
                              +'\n'
                              +lastError().driverText());
        return false;
    }

    return true;
}

bool DataBaseQuery::selectTable(QStringList *tabList,const QStringList *keyList,QString option)
{
    int i;
    QString str = "select DISTINCT ";
    if(tabList==NULL)
        return false;
    if(keyList==NULL)
    {
        str+="* ";
    }
    else
    {
        for(i=0;i<keyList->count()-1;i++)
        {
            str+=keyList->at(i);
            str+=',';
        }
        str+=keyList->at(i);

    }
    str+=" from ";
    for(i=0;i<tabList->count()-1;i++)
    {
        str+=tabList->at(i);
        str+=',';
    }
    str+=tabList->at(i);
    if(option.length()!=0)
        str+=" where " +option;
    if(exec(str)==false)
    {
        QMessageBox::critical(NULL,"Error",
                              lastError().databaseText()
                              +'\n'
                              +lastError().driverText());
        return false;
    }
    return true;
}


bool DataBaseQuery::insert(QString tabName, QMap<QString, QVariant> &dataMap)
{
    QString str = "insert into " +tabName + " (";
    QString valueStr = "values (";

    int i;
    QStringList keyList = dataMap.keys();
    QVariantList valueList = dataMap.values();
    for(i=0;i<keyList.count()-1;i++)
    {
        str+=keyList.at(i);
        str+=',';
        valueStr += "?";
        valueStr += ',';
    }
    str+=keyList.at(i);
    str+=") ";
    valueStr += "?";
    valueStr += ')';
    str+=valueStr;
    prepare(str);

    for(i=0;i<keyList.count();i++)
    {
        addBindValue(valueList.at(i));
    }

    if(exec()==0)
    {
        QMessageBox::critical(NULL,"Error",
                              lastError().databaseText()
                              +'\n'
                              +lastError().driverText());
        return false;
    }

    return true;
}

bool DataBaseQuery::execString(const QString &str)
{
    if (exec(str) == false)
    {
        QMessageBox::warning(0,"DataBase Error",lastError().databaseText()+'\n'+lastError().driverText());
        return false;
    }
    else
    {
        return true;
    }
}

bool DataBaseQuery::resetTable(QString tabName)
{
    //DELETE FROM sqlite_sequence WHERE name = 'TableName' --去掉自增序号
    QString query = "'";
    exec("delete  from sqlite_sequence WHERE name = " + query+tabName+query );
    return exec("delete  from " + tabName);
}

bool DataBaseQuery::resetAllTable()
{
    QStringList list = MyDataBase::getDataBase().tables();
    list.removeOne("sqlite_sequence");
   for(int i =0,t=list.count();i<t;i++)
   {
       resetTable(list.at(i));
   }
   return true;
}

int DataBaseQuery::size()
{
    if(last())
    {
        return at() + 1;
    }
    return 0;
}

bool DataBaseQuery::isItemExist(const QString &tabName,const QString &item,const QVariant &content)
{
    QString str = "select DISTINCT ";
    str+=item;
    str+=" from ";
    str+=tabName;
    str+=" where "+ item + "=?";
    prepare(str);
    addBindValue(content);
    if(exec()==0)
    {
        return false;
    }
    if(this->first())
        return true;
    return false;
}

bool DataBaseQuery::updateItem(QString tabName, QMap<QString,QVariant> &dataMap, QString option)
{
    QString str = "update " +tabName + " SET ";
    int i;
    QStringList keyList = dataMap.keys();
    QVariantList valueList = dataMap.values();
    int t = keyList.count()-1;
    for(i=0;i<t;i++)
    {
        str+=keyList.at(i);
        str+='=';
        str += "?";
        str += ',';
    }
    str+=keyList.at(i);
    str+="=";

    str += "?";
    if(option.length()!=0)
        str+=" where " +option;
    prepare(str);

    for(i=0,t=keyList.count();i<t;i++)
    {
        addBindValue(valueList.at(i));
        ////LOG_QDEBUG<<"1";
    }
    if(exec()==0)
    {
        QMessageBox::critical(NULL,"Error",
                              lastError().databaseText()
                              +'\n'
                              +lastError().driverText());
        return false;
    }
    return true;
}

bool DataBaseQuery::updateItem(QString tabName, QString fieldName, QString now, QMap<QString, QVariant> &dataMap_new)
{
    QString str="UPDATE "+tabName+" SET ";

    int i;
    QStringList keyList = dataMap_new.keys();//dataList.keys();
    QVariantList valueList = dataMap_new.values();//dataList.keys();

    for(i=0;i<keyList.count()-1;i++) {
        str+=keyList.at(i);
        // LOG_QDEBUG<<keyList.at(i);
        str+='=';
        str += "?";
        str += ',';
    }
    str+=keyList.at(i);
    str+="=";
    str += "?";
    if(now.count()!=0)
        str+=" WHERE "+fieldName+" = "+"\'"+now+"\'";

    prepare(str);
    for(i=0;i<keyList.count();i++)
    {
        addBindValue(valueList.at(i));
        //  LOG_QDEBUG<< valueList.at(i);
    }

    if(exec()==0)
    {
        return false;
    }
    return true;
}
